-- @owner: opentestcase042
-- @date: 2022/2/21
-- @testpoint: 创建索引，创建视图后，anlyze表

--step1:创建表，序列，插入数据;expect:成功
drop sequence if exists seq_analyze_table_20;
drop table if exists t_analyze_table_20 cascade;
create table t_analyze_table_20(
col_1 bigint, col_2 timestamp without time zone, col_3 bool,col_4 decimal,col_5 text ,col_6 smallint ,col_7 char(30),col_8 double precision,col_9 text,col_10 character varying(30),
col_11 bool ,col_12 bytea ,col_13 real ,col_14 numeric ,col_15 blob ,col_16 integer ,col_17 int ,col_18 timestamp with time zone ,col_19 binary_integer ,col_20 interval day to second ,
col_21 boolean, col_22 nchar(30) ,col_23 binary_integer ,col_24 nchar(100) ,col_25 character(1000) , ol_26 text , col_27 float ,col_28 float8 ,col_29 bigint ,col_30  timestamp with  time zone ,
col_31 timestamp , col_32 blob ,col_33 interval year to month, col_34 character(30) ,col_35 smallint ,col_36 blob ,col_37 char(300),col_38 float ,col_39 raw(100),col_40 clob ,
col_41 binary_double ,col_42 number(6,2) ,col_43 decimal(6,2) ,col_44 varchar2(50),col_45 varchar(30) ,col_46 varchar(100), col_47 numeric(12,6),col_48 varchar(30),col_49 date,col_50 blob ,
col_51 integer ,col_52 binary_double ,col_53 decimal(12,6),col_54 raw(8000),col_55 clob ,col_56 varchar2(8000) ,col_57 smalldatetime ,col_58 number(12,6),col_59 varchar(40000) ,col_60 raw(2000) ,
col_61 raw(200) ,col_62 smalldatetime ,col_63 raw(100) , col_64 varchar(1000),col_65 date,
constraint pk_id_analyze primary key(col_1)
) with (storage_type=ustore);

create sequence seq_analyze_table_20 increment by 1 start with 100000;

truncate table t_analyze_table_20;

begin
	for i in 1..1000 loop
      insert into t_analyze_table_20 values(
	  seq_analyze_table_20.nextval,
	  to_timestamp('12-sep-2014') + '1 hour',
	  true,
	  3.1415926+seq_analyze_table_20.nextval,
	  lpad('abc','5000','a@123&^%djgk'),
	  i,
	  lpad('abc','30','@'),
	  i+1.456789445455,
	  lpad('abc','5000','a@123&^%djgk'),
	  lpad('abc','30','b'),  --10
	  false,
	  lpad('10','5000','0101')::bytea,
	  3.1415926+seq_analyze_table_20.nextval,
	  i/4,
	  lpad('10','8000','01010')::blob,
	  i,
	  i,
	  to_timestamp('12-sep-2014') + '2 day',
	  '1',
	  (interval '4 5:12:10.222' day to second(3)), --20
	  0,
	  rpad('abc','30','e'),
	  seq_analyze_table_20.nextval,
	  rpad('abc','100','exc'),
	  lpad('abc','1000','a@123&^%djgk'),
	  lpad('abc','5000','a@123&^%djgk'),
	  i/4,seq_analyze_table_20.nextval-99,
	  i*3.1415,
	  to_timestamp('12-sep-2014') + '22 min',
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  lpad('abc','5000','010101111')::blob,
	  (interval '12' year),
	  rpad('abc','30','&'),
	  i,
	  lpad('10',5000,'01010')::blob,
	  rpad('abc','300','exc'),i/2.15,
	  rpad('0f',100,'aadb9')::raw,
	  lpad('abc','5000','a@123&^%djgk'),
	  1.0e+100,
	  3.14+i,
	  i+445.255,
	  rpad('abc','30','&'),
	  lpad('abc','30','&'),
	  rpad('abc','100','&gdsh'),
	  125563.141592,
	  rpad('abc','30','e'),
	  to_timestamp('12-sep-2014') + '1 day',lpad('abc','5000','010101111')::blob,
	  seq_analyze_table_20.nextval+2,-1.79e+100,98*0.99,lpad('10','8000','01010')::raw,lpad('abc','5000','a@123&^%djgk'),rpad('abc','8000','a@123&^%djgk'),to_timestamp('12-sep-2014') + '2 sec',25563.1415,lpad('abc','3000','a字符串@123&^%djgk'),lpad('10',2000,'01010')::raw,
	  lpad('10','200','01010')::raw,to_timestamp('12-sep-2014') + '2 hour','010101111111100000000000000',rpad('abc','1000','&gdsh'),to_timestamp('12-sep-2014') + '3 sec'
	  );

    end loop;
end;
/


--step2:创建索引，创建视图后，anlyze表;expect:成功
drop index if exists i_analyze_table_20;
create index i_analyze_table_20 on t_analyze_table_20(col_2,col_7);
drop view if exists view_analyze_table_20;
create view view_analyze_table_20 as select col_6,col_45 from t_analyze_table_20;
analyze t_analyze_table_20;

--step3:清理环境;expect:成功
drop sequence if exists seq_analyze_table_20;
drop index if exists i_analyze_table_20;
drop view if exists view_analyze_table_20;
drop table if exists t_analyze_table_20 cascade;